import pymysql


# 查询，默认的元组
def sele_all_def():
    # 连接数据库
    con = pymysql.connect(host='192.168.1.240', port=3306, user='root', password='123456', db='pnp_test_only',
                          charset='utf8')
    try:
        # 开启游标功能，创建游标对象并重命名
        with con.cursor() as cur:
            # 执行SQL语句,语句中的参数用%s占位，再传参数
            cur.execute('SELECT ver,date,barid FROM assistant_version');
            # fetchall返回的是个元组
            tup = cur.fetchall()
            for one in tup:
                print('版本--', one[0], '时间--', one[1], '网吧--', one[2])
    except pymysql.MySQLError as error:
        print(error)
        # 运行失败就回滚
        con.rollback()
    finally:
        # 断开数据库，释放资源
        con.close()


# 查询，元组转字典
def sele_all_dict():
    # 连接数据库,通过cursorclass=pymysql.cursors.DictCursor可以转元组为字典，通过key查询values
    con = pymysql.connect(host='192.168.1.240', port=3306, user='root', password='123456', db='pnp_test_only',
                          charset='utf8', cursorclass=pymysql.cursors.DictCursor)
    try:
        # 开启游标功能，创建游标对象并重命名
        with con.cursor() as cur:
            # 执行SQL语句,语句中的参数用%s占位，再传参数
            cur.execute('SELECT ver,date,barid FROM assistant_version');
            # fetchall返回的是个元组
            tup = cur.fetchall()
            for one in tup:
                # 通过字典key查值
                print(one['ver'], end='\t')
                print(one['date'], end='\t')
                print(one['barid'])
    except pymysql.MySQLError as error:
        print(error)
        # 运行失败就回滚
        con.rollback()
    finally:
        # 断开数据库，释放资源
        con.close()


if __name__ == '__main__':
    sele_all_def()
    sele_all_dict()
